﻿using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Base.DAL;
using MPSystem.Execl;
using MPSystem.Util;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;

namespace Base.BLL
{
    /// <summary>
    /// 超前应力数据服务
    /// </summary>
    class DataCqylService
    {
        private static readonly int _perPage = 10000;

        #region 超前应力在线监测
        /// <summary>
        /// 获取实时数据
        /// </summary>
        /// <param name="cqbh">测区编号</param>
        /// <param name="xdbh">巷道编号</param>
        /// <returns>所有传感器实时数据</returns>
        public CurveCqyl GetActive(int cqbh, int xdbh)
        {
            CurveCqyl rt = new CurveCqyl();
            DateTime dt = DateTime.Now;
            foreach (var _d in DataParseService.GetCache(DATA_TYPE.CQYL))
            {
                View_CQYL_Active d = _d as View_CQYL_Active;
                if (d == null) return null;
                if (d.CQBH != cqbh || d.XDBH != xdbh) continue;
                rt.X.Add(d.CQYLBH.ToString());
                rt.T.Add(d.CQDATETIME);
                rt.SD.Add(d.SD);
                rt.WZ.Add(d.CGQWZ);
                //超过规定时间无数据则视为故障
                int timeout = int.Parse(ConfigService.GetIni("Timeout", "600"));
                if ((dt - d.CQDATETIME).TotalSeconds >= timeout)
                {
                    rt.S.Add("离线");
                    rt.CQ.Add(0);
                    rt.WarnNow = true;
                }
                else
                {
                    rt.S.Add((d.CQDATA == 0) ? "故障" : "正常");
                    rt.CQ.Add(d.CQDATA);
                }
            }
            return rt;
            //示例：GetActive(1, 1);
        }

        /// <summary>
        /// 获取报警列表信息
        /// </summary>
        /// <param name="cqbh">测区编号</param>
        /// <param name="xdbh">巷道编号</param>
        /// <returns>包含报警信息的结果对象</returns>
        public CurveCqyl GetWarn(int cqbh, int xdbh)
        {
            CurveCqyl rt = new CurveCqyl();
            DateTime dt = DateTime.Now;
            foreach (var _d in DataParseService.GetCache(DATA_TYPE.CQYL))
            {
                View_CQYL_Active d = _d as View_CQYL_Active;
                if (d == null) return null;
                if (d.CQBH != cqbh || d.XDBH != xdbh) continue;
                //超过规定时间无数据则视为故障
                int timeout = int.Parse(ConfigService.GetIni("Timeout", "600"));
                if ((dt - d.CQDATETIME).TotalSeconds >= timeout)
                {
                    rt.S.Add("通讯故障，请检查设备状况");
                    rt.X.Add(d.CQYLBH.ToString());
                    rt.T.Add(d.CQDATETIME);
                    rt.WZ.Add(d.CGQWZ);
                }
            }
            return rt;
        }
        #endregion

        #region 超前应力数据查询
        /// <summary>
        /// 查询超前应力数据
        /// </summary>
        /// <param name="cqbh">测区编号</param>
        /// <param name="xdbh">巷道编号</param>
        /// <param name="bhs">编号列表</param>
        /// <param name="dtStart">起始时间</param>
        /// <param name="dtEnd">结束时间</param>
        /// <param name="jq">加权，0代表原始数据</param>
        /// <param name="where">条件语句，如"CQDATA>=10 AND CQDATA=20"</param>
        /// <param name="page">分页页数，从0开始</param>
        /// <param name="perPage">每页数量，默认20</param>
        /// <returns>超前应力数据列表</returns>
        public IEnumerable<View_CQYL_Active> Query(int cqbh, int xdbh, int[] bhs, DateTime dtStart, DateTime dtEnd, int jq, string where, int page, int perPage = 120)
        {
            int[] jqs = new int[] { 0, 2, 4, 8, 12, 24 };
            if (!jqs.Contains(jq))
            {
                return null;
            }
            string sql = @"SELECT CQDATETIME,CQYLBH,CGQWZ,SD,CQDATA
                            FROM (SELECT ROW_NUMBER() OVER(ORDER by CQDATETIME) ROWID,@tb.CQDATETIME,@tb.CQYLBH,CQYLCGQWZB.CGQWZ,CQYLCGQWZB.SD,@tb.CQDATA
                                FROM @tb INNER JOIN CQYLCGQWZB ON CQYLCGQWZB.CGQBH=@tb.CQYLBH
                                WHERE SYZT=1 AND @tb.CQBH=@cqbh AND CQYLCGQWZB.XDBH=@xdbh AND CQYLBH IN @bhs
                                AND CQDATETIME>=@dtStart AND CQDATETIME<=@dtEnd AND (@where)
                            ) Tb WHERE Tb.ROWID BETWEEN @page*@perPage+1 AND (@page+1)*@perPage";

            sql = sql.Replace("@tb", jq == 0 ? "CQYLSJB" : "CQYLSJB_JQ" + jq);
            string extra = where == null || where == string.Empty ? "1=1" : where;
            sql = sql.Replace("@where", extra);
            return Db.Query<View_CQYL_Active>(sql, new { cqbh = cqbh, xdbh = xdbh, bhs = bhs, dtStart = dtStart, dtEnd = dtEnd,page = page, perPage = perPage });
        }
        /// <summary>
        /// 查询超前应力数据
        /// </summary>
        /// <param name="cqbh">测区编号</param>
        /// <param name="xdbh">巷道编号</param>
        /// <param name="bh">传感器编号</param>
        /// <param name="dtStart">起始时间</param>
        /// <param name="dtEnd">结束时间</param>
        /// <param name="jq">加权，0代表原始数据</param>
        /// <returns>超前应力数据列表</returns>
        public IEnumerable<View_CQYL_Active> CurveNormal(int cqbh, int xdbh, int bh, DateTime dtStart, DateTime dtEnd, int jq)
        {
            int[] jqs = new int[] { 0, 2, 4, 8, 12, 24 };
            if (!jqs.Contains(jq))
            {
                return null;
            }
            string sql = @"SELECT @tb.CQDATETIME,@tb.CQYLBH,CQYLCGQWZB.CGQWZ,CQYLCGQWZB.SD,@tb.CQDATA
                                FROM @tb INNER JOIN CQYLCGQWZB ON CQYLCGQWZB.CGQBH=@tb.CQYLBH
                                WHERE SYZT=1 AND @tb.CQBH=@cqbh AND CQYLCGQWZB.XDBH=@xdbh AND CQYLBH=@bh
                                AND CQDATETIME>=@dtStart AND CQDATETIME<=@dtEnd";

            sql = sql.Replace("@tb", jq == 0 ? "CQYLSJB" : "CQYLSJB_JQ" + jq);
            return Db.Query<View_CQYL_Active>(sql, new { cqbh = cqbh, xdbh = xdbh, bh = bh, dtStart = dtStart, dtEnd = dtEnd });
        }
        /// <summary>
        /// 查询超前应力数据总数
        /// </summary>
        /// <param name="cqbh">测区编号</param>
        /// <param name="xdbh">巷道编号</param>
        /// <param name="bhs">编号列表</param>
        /// <param name="dtStart">起始时间</param>
        /// <param name="dtEnd">结束时间</param>
        /// <param name="jq">加权，0代表原始数据</param>
        /// <param name="where">条件语句，如"CQDATA>=10 AND CQDATA=20"</param>
        /// <returns>超前应力数据列表</returns>
        public int QueryCount(int cqbh, int xdbh, int[] bhs, DateTime dtStart, DateTime dtEnd, int jq, string where)
        {
            int[] jqs = new int[] { 0, 2, 4, 8, 12, 24 };
            if (!jqs.Contains(jq))
            {
                return 0;
            }
            string sql = @"SELECT COUNT(1) FROM @tb INNER JOIN CQYLCGQWZB ON CQYLCGQWZB.CGQBH=@tb.CQYLBH
                                WHERE SYZT=1 AND @tb.CQBH=@cqbh AND CQYLCGQWZB.XDBH=@xdbh AND CQYLBH IN @bhs
                                AND CQDATETIME>=@dtStart AND CQDATETIME<=@dtEnd AND (@where)";

            sql = sql.Replace("@tb", jq == 0 ? "CQYLSJB" : "CQYLSJB_JQ" + jq);
            string extra = where == null || where == string.Empty ? "1=1" : where;
            sql = sql.Replace("@where", extra);
            return Db.Count(sql, new { cqbh = cqbh, xdbh = xdbh, bhs = bhs, dtStart = dtStart, dtEnd = dtEnd});
        }
        #endregion

        #region 超前应力数据导出
        /// <summary>
        /// 导出超前应力数据
        /// </summary>
        /// <param name="saveFileName">导出文件保存位置</param>
        /// <param name="cqbh">测区编号</param>
        /// <param name="xdbh">巷道编号</param>
        /// <param name="bhs">编号列表</param>
        /// <param name="dtStart">起始时间</param>
        /// <param name="dtEnd">结束时间</param>
        /// <param name="jq">加权，0代表原始数据</param>
        /// <param name="where">条件语句，如"CQDATA>=10 AND CQDATA=20"</param>
        /// <returns>超前应力数据列表</returns>
        public void Export(string saveFileName, int cqbh, int xdbh, int[] bhs, DateTime dtStart, DateTime dtEnd, int jq, string where)
        {
            //复制模板
            string dir = System.AppDomain.CurrentDomain.BaseDirectory;// System.Environment.CurrentDirectory;
            string tpl = dir + @"\Tpl\Query_WYYL.xls";
            //File.Copy(tpl, saveFileName);
            int page = 0;

            //设置字体，大小，对齐方式
            using (var ftpl = File.OpenRead(tpl))
            {
                //创建主要对象
                var workbook = new HSSFWorkbook(ftpl);
                var sheet = workbook.GetSheetAt(0);
                sheet.GetRow(1).GetCell(3).SetCellValue(DateTime.Now.ToString("报表日期 yyyy-MM-dd HH:mm:ss"));

                HSSFCellStyle style = ExcelHelper.DefaultStyle(workbook);
                HSSFCellStyle _style = (HSSFCellStyle)workbook.CreateCellStyle();
                _style.CloneStyleFrom(style);
                _style.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");

                int row = 3;
                while (true)
                {
                    var data = Query(cqbh, xdbh, bhs, dtStart, dtEnd, jq, where, page++, _perPage);
                    if (data == null || data.Count() == 0)
                        break;
                    int i = 0;
                    for (; i < data.Count(); i++)
                    {
                        var dataRow = sheet.CreateRow(row + i);
                        int j = 0;
                        var d = data.ElementAt(i);
                        dataRow.CreateCell(j++).SetCellValue(d.CQDATETIME.ToString("yyyy-MM-dd HH:mm:ss"));
                        dataRow.CreateCell(j++).SetCellValue(d.CQYLBH);
                        dataRow.CreateCell(j++).SetCellValue(d.CGQWZ);
                        dataRow.CreateCell(j++).SetCellValue(d.SD);
                        dataRow.CreateCell(j++).SetCellValue(d.CQDATA == 0 ? "--" : d.CQDATA.ToString("f2"));
                        for (j--; j >= 0; j--)
                        {
                            if (j >=3)
                            {
                                dataRow.GetCell(j).CellStyle = _style;
                            }
                            else
                            {
                                dataRow.GetCell(j).CellStyle = style;
                            }
                        }
                    }
                    row = row + i;
                }
                using (var fout = File.OpenWrite(saveFileName))
                {
                    workbook.Write(fout);
                }
            }
        }

        /// <summary>
        /// 超前应力报表参数
        /// </summary>
        public class ExportParam
        {
            public string ComanpyName = "";//公司名称
            public string XD = "";//巷道名称
            public string RZBH = "";//认证编号
            public bool WithChart = false;//是否生成曲线
            public string Sign = "";//签名
        }

        /// <summary>
        /// 导出超前应力报表
        /// </summary>
        /// <param name="saveFileName">导出文件保存位置</param>
        /// <param name="param">报表参数</param>
        /// <param name="cqbh">测区编号</param>
        /// <param name="xdbh">巷道编号</param>
        /// <param name="bhs">传感器编号列表</param>
        /// <param name="dtStart">起始时间</param>
        /// <param name="dtEnd">结束时间</param>
        /// <returns></returns>
        public void ExportReport(string saveFileName, ExportParam param, int cqbh, int xdbh, int[] bhs, DateTime dtStart, DateTime dtEnd)
        {
            //复制模板
            string dir = System.AppDomain.CurrentDomain.BaseDirectory; //System.Environment.CurrentDirectory;
            string tpl = dir + (param.WithChart ? @"\Tpl\Report_WYYL_Chart.xls" : @"\Tpl\Report_WYYL.xls");
            //File.Copy(tpl, saveFileName);
            //图表下的第一行
            int rowAfterChart = param.WithChart ? 10 : 3;
            using (var ftpl = File.OpenRead(tpl))
            {
                //创建主要对象
                var workbook = new HSSFWorkbook(ftpl);
                var sheet = workbook.GetSheetAt(0);
                //创建基本样式
                HSSFCellStyle style = ExportExcel.DefaultStyle(workbook);
                HSSFCellStyle _style = (HSSFCellStyle)workbook.CreateCellStyle();
                _style.CloneStyleFrom(style);
                IDataFormat dataformat = workbook.CreateDataFormat();
                _style.DataFormat = dataformat.GetFormat("0.0");
                //修改基本参数
                sheet.GetRow(0).GetCell(0).SetCellValue(param.ComanpyName);
                sheet.GetRow(1).GetCell(4).SetCellValue(DateTime.Now.ToString("报表日期 yyyy-MM-dd HH:mm:ss"));
                sheet.GetRow(2).GetCell(0).SetCellValue(
                    string.Format("巷道名称:{0}   认证编号:{1}   监测日期:{2}至{3}",
                    param.XD, param.RZBH, dtStart.ToString("yyyy-MM-dd HH:mm:ss"), dtEnd.ToString("yyyy-MM-dd HH:mm:ss"))
                );
                //签名
                sheet.GetRow(rowAfterChart + 17).GetCell(0).SetCellValue(param.Sign);


                //填写监测数据统计表
                var rt = Report(cqbh, xdbh, bhs, dtStart, dtEnd);
                int rowList = rowAfterChart + 2;
                IRow mySourceRow = sheet.GetRow(rowList);
                ExcelHelper.InsertRow(sheet, rowList, rt.CGQBH.Count() - 1, mySourceRow);
                for (int i = 0; i < rt.CGQBH.Count(); i++)
                {
                    int j = 0;
                    var dataRow = sheet.GetRow(rowList + i);
                    dataRow.CreateCell(j++).SetCellValue(rt.CGQBH[i]);
                    dataRow.CreateCell(j++).SetCellValue(rt.WZ[i]);
                    dataRow.CreateCell(j++).SetCellValue(rt.SD[i]);
                    dataRow.CreateCell(j++).SetCellValue(rt.CQStart[i]);
                    dataRow.CreateCell(j++).SetCellValue(rt.CQEnd[i]);
                    dataRow.CreateCell(j++).SetCellValue(rt.CQEnd[i] - rt.CQStart[i]);

                    for (j--; j >= 0; j--)
                    {
                        if (j >= 2)
                        {
                            dataRow.GetCell(j).CellStyle = _style;
                        }
                        else
                        {
                            dataRow.GetCell(j).CellStyle = style;
                        }
                    }
                }

                //保存
                using (var fout = File.OpenWrite(saveFileName))
                {
                    workbook.Write(fout);
                }
            }
        }
        #endregion

        #region 超前应力监测报表
        /// <summary>
        /// 获取超前应力报表
        /// </summary>
        /// <param name="cqbh">测区编号</param>
        /// <param name="xdbh">巷道编号</param>
        /// <param name="bhs">传感器编号列表</param>
        /// <param name="dtStart">起始时间</param>
        /// <param name="dtEnd">结束时间</param>
        /// <returns>返回超前应力报表所需所有数据</returns>
        public ReportCqyl Report(int cqbh, int xdbh, int[] bhs, DateTime dtStart, DateTime dtEnd)
        {
            //对传感器编号进行排序
            Array.Sort(bhs);
            string sqlTpl = @"SELECT @limit @tb.CQDATETIME,@tb.CQYLBH,CQYLCGQWZB.CGQWZ,CQYLCGQWZB.SD,@tb.CQDATA
                                FROM @tb INNER JOIN CQYLCGQWZB ON CQYLCGQWZB.CGQBH=@tb.CQYLBH
                                WHERE SYZT=1 AND @tb.CQBH=@cqbh AND CQYLCGQWZB.XDBH=@xdbh AND @condbh
                                AND CQDATETIME>=@dtStart AND CQDATETIME<=@dtEnd
                                ORDER BY CQDATETIME";
            sqlTpl = sqlTpl.Replace("@tb", "CQYLSJB");
            string sql = sqlTpl.Replace("@limit", "TOP 1").Replace("@condbh", "CQYLBH=@bh");
            ReportCqyl rt = new ReportCqyl();
            foreach (int bh in bhs)
            {
                //起始值
                var d = Db.Find<View_CQYL_Active>(sql, new { cqbh = cqbh, xdbh = xdbh, bh = bh, dtStart = dtStart, dtEnd = dtEnd });
                if (d == null) continue;
                rt.CGQBH.Add(bh);
                rt.SD.Add(d.SD);
                rt.WZ.Add(d.CGQWZ);
                rt.CQStart.Add(d.CQDATA);
                //当前值
                var _d = Db.Find<View_CQYL_Active>(sql + " DESC", new { cqbh = cqbh, xdbh = xdbh, bh = bh, dtStart = dtStart, dtEnd = dtEnd });
                rt.CQEnd.Add(_d.CQDATA);
                //增加值
                rt.DCQ.Add(_d.CQDATA - d.CQDATA);
            }
            return rt;
        }
        #endregion

        public static void Test()
        {
            var rt = new DataCqylService().GetActive(1, 1);
            IEnumerable<View_CQYL_Active> list = new DataCqylService().Query(1, 1, new int[] { 1, 2, 3, 4, 5 }, new DateTime(2018, 12, 23), new DateTime(2018, 12, 24), 0, "", 0);
            ReportCqyl report = new DataCqylService().Report(1, 1, new int[] { 1, 2, 3, 4, 5 }, new DateTime(2018, 12, 23), new DateTime(2018, 12, 24));
            Console.WriteLine();
        }
    }

    #region 保存结果的类
    /// <summary>
    /// 超前应力查询返回结果，用于绘图
    /// </summary>
    public class CurveCqyl
    {
        /// <summary>
        /// 是否报警
        /// </summary>
        public bool WarnNow = false;
        /// <summary>
        /// 时间
        /// </summary>
        public List<DateTime> T = new List<DateTime>();
        /// <summary>
        /// 横轴标记
        /// </summary>
        public List<string> X = new List<string>();
        /// <summary>
        /// 工作状态
        /// </summary>
        public List<string> S = new List<string>();
        /// <summary>
        /// 安装位置
        /// </summary>
        public List<string> WZ = new List<string>();
        /// <summary>
        /// 深度
        /// </summary>
        public List<double> SD = new List<double>();
        #region 超前应力
        public List<double> CQ = new List<double>();
        #endregion
    }
    /// <summary>
    /// 超前应力报表
    /// </summary>
    public class ReportCqyl
    {
        /// <summary>
        /// 传感器编号
        /// </summary>
        public List<int> CGQBH = new List<int>();
        /// <summary>
        /// 深度
        /// </summary>
        public List<double> SD = new List<double>();
        /// <summary>
        /// 安装位置
        /// </summary>
        public List<string> WZ = new List<string>();
        /// <summary>
        /// 起始应力值
        /// </summary>
        public List<double> CQStart = new List<double>();
        /// <summary>
        /// 当前应力值
        /// </summary>
        public List<double> CQEnd = new List<double>();
        /// <summary>
        /// 变化值
        /// </summary>
        public List<double> DCQ = new List<double>();        
    }
    #endregion
}